/*
 *                    BioJava development code
 *
 * This code may be freely distributed and modified under the
 * terms of the GNU Lesser General Public Licence.  This should
 * be distributed with the code.  If you do not have a copy,
 * see:
 *
 *      http://www.gnu.org/copyleft/lesser.html
 *
 * Copyright for this code is held jointly by the individual
 * authors.  These should be listed in @author doc comments.
 *
 * For more information on the BioJava project and its aims,
 * or to join the biojava-l mailing list, visit the home page
 * at:
 *
 *      http://www.biojava.org/
 *
 * Created on 15.04.2004
 * @author Andreas Prlic
 *
 */
package dasregistry;

import java.util.*;
import java.sql.*;

import javax.sql.DataSource;
import org.biojava.services.das.registry.*;
import javax.naming.Context;
import javax.naming.InitialContext;

public class statisticsBean {
	private String DATASOURCE_NAME;

	protected DataSource dataSource;

	private String totalServersSql = " SELECT COUNT(*) from registry";

	private String uniqueUrlSql = "SELECT DISTINCT(url) from registry";

	private String allTypesCount = "select count(*) from  registry r, types t where r.auto_id=t.auto_id";
	private String sourcesWithTypesCount = "select count(distinct(r.auto_id))from registry r, types t where r.auto_id=t.auto_id";

	public statisticsBean() {
		//System.out.println("statisticsBean init");
		DATASOURCE_NAME = DasRegistrySql.DATASOURCE_NAME;
		try {
			Context env = (Context) new InitialContext()
					.lookup("java:comp/env");
			dataSource = (DataSource) env.lookup(DATASOURCE_NAME);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public int getTotalNumberServers() {
		int returnvalue = 0;
		Connection conn = null;
		try {
			conn = dataSource.getConnection();

			PreparedStatement ps = conn.prepareStatement(totalServersSql);
			ResultSet row = ps.executeQuery();
			row.next();
			returnvalue = row.getInt(1);
			row.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return returnvalue;

	}

	private String[] getUniqueServers(Connection conn) throws SQLException {
		PreparedStatement ps = conn.prepareStatement(uniqueUrlSql);
		ResultSet row = ps.executeQuery();
		List lst = new ArrayList();
		while (row.next()) {
			String s = row.getString(1);
			String[] spl = s.split("/");
			String u = spl[2];
			lst.add(u);
		}
		row.close();
		ps.close();
		return (String[]) lst.toArray(new String[lst.size()]);

	}

	private String getCountry(String c) {
		String[] spl = c.split(":");
		if (spl.length > 0)
			return spl[0];
		else
			return c;
	}

	public String[] getInstitutions() {
		Connection conn = null;
		Map institutions = new TreeMap();
		try {
			conn = dataSource.getConnection();
			String[] urls = getUniqueServers(conn);

			for (int i = 0; i < urls.length; i++) {
				String url = urls[i];
				String[] spl = url.split("\\.");
				int l = spl.length;
				if (l >= 3) {
					String country = getCountry(spl[l - 1]);
					String inst = spl[l - 3] + "." + spl[l - 2] + "." + country;

					institutions.put(inst, "1");

				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return (String[]) institutions.keySet().toArray(
				new String[institutions.size()]);

	}

	public String[] getCountries() {

		Connection conn = null;
		Map countries = new TreeMap();
		try {
			conn = dataSource.getConnection();
			String[] urls = getUniqueServers(conn);

			for (int i = 0; i < urls.length; i++) {
				String url = urls[i];
				String[] spl = url.split("\\.");
				if (spl.length > 0) {
					String country = getCountry(spl[spl.length - 1]);
					countries.put(country, "1");
				} else {
					// System.out.println("hum " + url);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

		Set s = countries.keySet();
		return (String[]) s.toArray(new String[s.size()]);

	}

	public int getTypesCount() {
		Connection conn = null;
		int count = 0;
		try {
			conn = dataSource.getConnection();
			Statement statmnt = conn.createStatement();
			ResultSet result = statmnt.executeQuery(allTypesCount);
			while (result.next()) {
				count = result.getInt(1);
			}
			conn.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;

	}
	
	public int getSourcesWithTypes(){
		Connection conn = null;
		int count = 0;
		try {
			conn = dataSource.getConnection();
			Statement statmnt = conn.createStatement();
			ResultSet result = statmnt.executeQuery(sourcesWithTypesCount);
			while (result.next()) {
				count = result.getInt(1);
			}
			conn.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}
}
